package vip.xiaonuo.smzq.modular.nbfb.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.kcl.entity.Zycl;
import vip.xiaonuo.smzq.modular.nbfb.entity.vo.KcdKccdtjVo;

import java.util.List;

@Mapper
public interface ZyclSevenMapper extends BaseMapper<Zycl> {

    /**
     * 年报附表七查询数据
     * @param nd
     * @return
     */
    @Select("with base as (\n" +
            "select ks.kqbh, ks.djflbh, ks.xzqdm, ks.kcjdmc,\n" +
            " case when  LEFT(ks.kqbh, 4) in ('1324','1326') then '130600' else  LEFT(ks.kqbh, 4) ||'00' end xzqbh,  ks.kcmc, ks.jsdxmc, ks.kcdm, ks.tjdx,zy.zycllx,\n" +
            " case when kc.xsbs = 0 then zy.jsldw else zy.ksldw end dw,\n" +
            " case when kc.xsbs = 0 then nmbyjsl else nmbyksl end nmby\n" +
            "from hisi_kshz ks\n" +
            "left join hisi_ks_zyclhz zy\n" +
            "on ks.nd = zy.nd and ks.kqbh = zy.kqbh and ks.djflbh = zy.djflbh and ks.kcdm = zy.kcdm and ks.tjdx = zy.tjdx\n" +
            "left join kcl_cb06_kc kc on ks.kcdm=kc.kcdm and ks.tjdx = kc.tjdx\n" +
            "where ks.nd = #{nd} and ks.sbbsm<>3 and zy.zycllx IN ('TM', 'KZ', 'TD')\n" +
            "),\n" +
            "\n" +
            "tmp1 as (\n" +
            "select kcmc, jsdxmc, kcdm, tjdx, xzqbh, kqbh, max(dw) dw, max(kcjdmc) kcjdmc from base \n" +
            "group by kcmc, jsdxmc, kcdm, tjdx, xzqbh, kqbh\n" +
            "),\n" +
            "\n" +
            "head1 as (\n" +
            "select tmp1.xzqbh, xzq.mc, kcmc, case when kcdm in (42201, 42202, 32006, 63701) then '' else jsdxmc end jsdxmc, kcdm, case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end tjdx, max(dw) dw, \n" +
            " sum(case when kcjdmc='普查' then 1 else 0 end) dxs,\n" +
            " sum(case when kcjdmc='详查' then 1 else 0 end) zxs,\n" +
            " sum(case when kcjdmc='勘探' then 1 else 0 end) xxs\n" +
            "from tmp1 \n" +
            "left join kcl_cb08_xzqh xzq on tmp1.xzqbh = xzq.dm::varchar\n" +
            "group by kcmc, case when kcdm in (42201, 42202, 32006, 63701) then '' else jsdxmc end, kcdm, case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end, tmp1.xzqbh, xzq.mc\n" +
            "),\n" +
            "\n" +
            "head2 as (\n" +
            "select xzq.fdm, kcmc, jsdxmc, kcdm, tjdx, max(dw) dw, sum(dxs) dxs, sum(zxs) zxs, sum(xxs) xxs\n" +
            "from head1 \n" +
            "left join kcl_cb08_xzqh xzq on head1.xzqbh = xzq.dm::varchar\n" +
            "group by kcmc, jsdxmc, kcdm, tjdx, xzq.fdm\n" +
            "),\n" +
            "\n" +
            "head as (\n" +
            "select head2.fdm::varchar, xzq.mc, kcmc, jsdxmc, kcdm, tjdx, dw, dxs, zxs, xxs \n" +
            "from head2\n" +
            "left join kcl_cb08_xzqh xzq on head2.fdm = xzq.dm\n" +
            "union \n" +
            "select * from head1\n" +
            "),\n" +
            "\n" +
            "\n" +
            "data1 as (\n" +
            "select kcdm, case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end tjdx, xzq.fdm, \n" +
            "       sum(case when kcjdmc='普查' then nmby else 0 end) dxl,\n" +
            "       sum(case when kcjdmc='详查' then nmby else 0 end) zxl,\n" +
            "       sum(case when kcjdmc='勘探' then nmby else 0 end) xxl\n" +
            "from base\n" +
            "left join kcl_cb08_xzqh xzq on base.xzqdm = xzq.dm::varchar\n" +
            "group by  kcdm, case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end, xzq.fdm\n" +
            "),\n" +
            "\n" +
            "data2 as(\n" +
            "select kcdm, tjdx, xzq.fdm, sum(dxl) dxl, sum(zxl) zxl, sum(xxl) xxl\n" +
            "from data1\n" +
            "left join kcl_cb08_xzqh xzq on data1.fdm = xzq.dm\n" +
            "group by kcdm, tjdx, xzq.fdm\n" +
            "),\n" +
            "\n" +
            "\n" +
            "alldata as(\n" +
            "select * from data1\n" +
            "UNION\n" +
            "select * from data2\n" +
            ")\n" +
            "\n" +
            "\n" +
            "select head.mc as xzqhmc,(CASE  WHEN head.jsdxmc <> '' THEN CONCAT(head.kcmc, ' (', head.jsdxmc, ')')  ELSE head.kcmc END )AS kcmc, head.dw, head.dxs AS pcCount, cast(dxl AS DECIMAL(18,2)) as pcNmby, head.zxs as xcCount, cast(zxl AS DECIMAL(18,2)) as xcNmby , head.xxs ktCount, cast(xxl AS DECIMAL(18,2)) as ktNmby,\n" +
            "(CASE WHEN head.tjdx :: VARCHAR IS NOT NULL AND head.tjdx :: VARCHAR <> '' THEN CONCAT ( head.kcdm :: VARCHAR, '_', head.tjdx :: VARCHAR) ELSE head.kcdm :: VARCHAR END ) AS bz \n" +
            "\n" +
            "from head \n" +
            "left join alldata on head.kcdm=alldata.kcdm and head.tjdx=alldata.tjdx and head.fdm = alldata.fdm::varchar\n" +
            "order by head.kcdm, head.tjdx, head.fdm\n")
    List<KcdKccdtjVo> getNbfbseven(Integer nd);
}
